package com.a51integrated.sfs2x;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.smartfoxserver.v2.core.SFSEventParam;
import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.entities.Room;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.entities.data.ISFSObject;
import com.smartfoxserver.v2.extensions.BaseClientRequestHandler;
import com.smartfoxserver.v2.extensions.ExtensionLogLevel;

public class ExitSessionRequestHandler extends BaseClientRequestHandler {

	@Override
	public void handleClientRequest(User player, ISFSObject params) {
		// TODO Auto-generated method stub
		
		String username = player.getName();
		
		Room last_room = player.getLastJoinedRoom();
		if (last_room != null)
		{			
			// Get password from DB.
			IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
			Connection connection;
			try
			{
				// Grab a connection from the DBManager connection pool.
				connection = dbManager.getConnection();

				// --------------------------------------------------------
				// --------------------------------------------------------
				// --------------------------------------------------------
				
				// Remove the disconnected player from the game he was in in the games table, and decrement the numconnected value of that row by 1.
				PreparedStatement stmt = connection.prepareStatement("SELECT `names` FROM `games` WHERE player0='" + username + "' OR player1='" + username + "' OR player2='" + username + "' OR player3='" + username + "'");
				ResultSet res = stmt.executeQuery();
				
				if (!res.first())
				{
					// The player's name is not present anywhere in the games table. No need to remove him.
					trace("User disconnected, user '" + username + "' does not need to be deleted from any records");
				}
				else
				{
					boolean first = true;
					do
					{
						if (!first)
						{
							res.next();
						}
						
						String gameName = res.getString("names");
						
						trace("User disconnected, deleting user '" + username + "' from records for game '" + gameName + "'");
						
						// Now that gameName contains the name of a game that the player was part of, remove the player's name from this game and decrement the numconnected value.
						stmt = connection.prepareStatement("UPDATE `games` SET numconnected=(numconnected - 1) WHERE names='" + gameName + "'");
						stmt.execute();
						stmt = connection.prepareStatement("UPDATE `games` SET player0='' WHERE player0='" + username + "'");
						stmt.execute();
						stmt = connection.prepareStatement("UPDATE `games` SET player1='' WHERE player1='" + username + "'");
						stmt.execute();
						stmt = connection.prepareStatement("UPDATE `games` SET player2='' WHERE player2='" + username + "'");
						stmt.execute();
						stmt = connection.prepareStatement("UPDATE `games` SET player3='' WHERE player3='" + username + "'");
						stmt.execute();
						
						// Now remove the player from any table that contains game data.
						stmt = connection.prepareStatement("UPDATE `game_" + gameName + "_gamedata` SET owner='' WHERE owner='" + username + "'");
						stmt.execute();
						stmt = connection.prepareStatement("DELETE FROM `game_" + gameName + "_players` WHERE player='" + username + "'");
						stmt.execute();
					}
					while (!res.isLast());
				}
				
				// Now remove any redundant game tables.
				{
					// Firstly, count the number of rows in the first column of table games.
					stmt = connection.prepareStatement("SELECT COUNT('names') FROM games");
					res = stmt.executeQuery();
					
					// Verify that one record was found.
					if (!res.first())
					{
						connection.close();
						return;
					}
					
					// Store the number of rows. (Index starts at 1)
					int NumRows = res.getInt(1);
					
					// Now obtain the data itself.
					stmt = connection.prepareStatement("SELECT * FROM games");
					res = stmt.executeQuery();
					
					if (!res.first())
					{
						connection.close();
						return;
					}
					
					Collection<String> deprecated_names = new ArrayList<String>();	// Collection of game names that are now deprecated and should be removed from the database.
					
					// Get the information of the games. This loop is also used to construct a list of
					// games that are no longer in use (0 connected players).
					// These games must not be sent to the player and must be removed from the database (along with all tables associated with them).
					for (int i = 0; i < NumRows; ++i)
					{
						String name = res.getString("names");
						int connected = res.getInt("numconnected");
						
						if (connected <= 0)
						{
							deprecated_names.add(name);
						}
						
						// Advance the cursor.
						res.next();
					}
					
					// Now clean up any deprecated games and tables.
					if (deprecated_names.size() > 0)
					{
						String removeGamesString = "DELETE FROM games WHERE ";
						for (Iterator it = deprecated_names.iterator(); it.hasNext(); )
						{
							String iterator_value = (String)it.next();
							removeGamesString += "names='" + iterator_value + "' OR ";
							
							// Send two statements to delete the tables that were created specifically for a game.
							stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_players`");
							stmt.execute();
							stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_gamedata`");
							stmt.execute();
						}
						// Add a useless part to the end of the statement because there's a trailing || operator.
						removeGamesString += "0=1";
						
						// Now prepare and execute the statement to delete rows from the games table.
						stmt = connection.prepareStatement(removeGamesString);
						stmt.execute();
					}
				}

				// --------------------------------------------------------
				// --------------------------------------------------------
				// --------------------------------------------------------
				
				connection.close();			
			}
			catch(SQLException e)
			{
				trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
			}
		}
		else
		{
			trace("No valid room found for user request.");
		}
	}

}
